﻿using Infrastructures;
using IRepository;
using Models;
using Spring.Data.Common;
using Spring.Data.Generic;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RepositoryMySQL
{
    public class StudentRepository : IStudentRepository
    {
        private string baseStudent = "select StudentNO,Name,StudentLDNO,StudentGrade,Status,Remark,Education,SchoolNO,Major,EntranceDate,UpdateTime,CreateTime from Student";
        public Student Create(Student obj)
        {
            string strIns = @"insert into Student(StudentNO,Name,StudentLDNO,StudentGrade,Status,Remark,Education,SchoolNO,Major,EntranceDate,UpdateTime,CreateTime) values(@StudentNO,@Name,@StudentLDNO,@StudentGrade,@Status,@Remark,@Education,@SchoolNO,@Major,@EntranceDate,@UpdateTime,@CreateTime);";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            p.Add("StudentNO", DbType.Int32).Value = obj.StudentNO;
            p.Add("Name", DbType.String).Value = obj.Name;
            p.Add("StudentLDNO", DbType.String).Value = obj.StudentLDNO;
            p.Add("StudentGrade", DbType.String).Value = obj.StudentGrade;
            p.Add("Status", DbType.String).Value = obj.Status;
            p.Add("Remark", DbType.String).Value = obj.Remark;
            p.Add("Education", DbType.String).Value = obj.Education;
            p.Add("SchoolNO", DbType.Int32).Value = obj.SchoolNO;
            p.Add("Major", DbType.String).Value = obj.Major;
            p.Add("EntranceDate", DbType.DateTime).Value = obj.EntranceDate;
            p.Add("UpdateTime", DbType.DateTime).Value = obj.UpdateTime;
            p.Add("CreateTime", DbType.DateTime).Value = obj.CreateTime;

            DBHelper.TemplateMySQL.ExecuteNonQuery(CommandType.Text, strIns, p);

            return obj;
        }
        public void Update(Student obj)
        {
            string strUpdate = @"update Student set Name=@Name,StudentLDNO=@StudentLDNO,StudentGrade=@StudentGrade,Status=@Status,Remark=@Remark,Education=@Education,SchoolNO=@SchoolNO,Major=@Major,EntranceDate=@EntranceDate,UpdateTime=@UpdateTime,CreateTime=@CreateTime where StudentNO=@StudentNO";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();

            p.Add("StudentNO", DbType.Int32).Value = obj.StudentNO;
            p.Add("Name", DbType.String).Value = obj.Name;
            p.Add("StudentLDNO", DbType.String).Value = obj.StudentLDNO;
            p.Add("StudentGrade", DbType.String).Value = obj.StudentGrade;
            p.Add("Status", DbType.String).Value = obj.Status;
            p.Add("Remark", DbType.String).Value = obj.Remark;
            p.Add("Education", DbType.String).Value = obj.Education;
            p.Add("SchoolNO", DbType.Int32).Value = obj.SchoolNO;
            p.Add("Major", DbType.String).Value = obj.Major;
            p.Add("EntranceDate", DbType.DateTime).Value = obj.EntranceDate;
            p.Add("UpdateTime", DbType.DateTime).Value = obj.UpdateTime;
            p.Add("CreateTime", DbType.DateTime).Value = obj.CreateTime;

            DBHelper.TemplateMySQL.ExecuteNonQuery(CommandType.Text, strUpdate, p);
        }
        public Student Get(string studentno)
        {
            string strSel = baseStudent + " where StudentNO=@StudentNO";

            IDbParameters p = DBHelper.TemplateMySQL.CreateDbParameters();
            p.Add("StudentNO", DbType.Int32).Value = studentno.ToInt();


            IList<Student> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<Student>(MapStudent), p);
            return lst.Count == 0 ? null : lst[0];
        }
        public IList<Student> List()
        {
            string strSel = baseStudent;

            IList<Student> lst = DBHelper.TemplateMySQL.QueryWithRowMapperDelegate(CommandType.Text, strSel, new RowMapperDelegate<Student>(MapStudent));

            return lst;
        }

        private Student MapStudent(IDataReader dr, int count)
        {
            Student obj = new Student();
            obj.StudentNO = dr.GetInt32(0);
            obj.Name = dr.IsDBNull(1) ? String.Empty : dr.GetString(1);
            obj.StudentLDNO = dr.IsDBNull(2) ? String.Empty : dr.GetString(2);
            obj.StudentGrade = dr.IsDBNull(3) ? String.Empty : dr.GetString(3);
            obj.Status = (CommonStatus)Enum.Parse(typeof(CommonStatus), dr.GetString(4), true);
            obj.Remark = dr.IsDBNull(5) ? String.Empty : dr.GetString(5);
            obj.Education = dr.IsDBNull(6) ? String.Empty : dr.GetString(6);
            obj.SchoolNO = dr.GetInt32(7);
            obj.Major = dr.IsDBNull(8) ? String.Empty : dr.GetString(8);
            obj.EntranceDate = dr.GetDateTime(9);
            obj.UpdateTime = dr.GetDateTime(10);
            obj.CreateTime = dr.GetDateTime(11);

            return obj;
        }  


    }
}
